package com.zjy.financialsystemjavafx.service;

import com.zjy.financialsystemjavafx.model.entity.Transaction;
import com.zjy.financialsystemjavafx.util.DatabaseUtil;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 交易服务层，负责收支记录的增删改查
 */
public class TransactionService {
    private final DataSource dataSource;

    public TransactionService() {
        this.dataSource = DatabaseUtil.getDataSource();
    }

    /**
     * 获取指定用户的所有交易记录
     */
    public List<Transaction> getTransactions(int userId) throws Exception {
        String sql = "SELECT t.transaction_id, t.transaction_type, t.amount, t.transaction_date, " +
                     "a.account_name, c.category_name, t.description " +
                     "FROM transactions t " +
                     "JOIN accounts a ON t.account_id = a.account_id " +
                     "JOIN categories c ON t.category_id = c.category_id " +
                     "WHERE t.user_id = ? ORDER BY t.transaction_date DESC";
        List<Transaction> list = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Transaction t = new Transaction();
                    t.setTransactionId(rs.getInt(1));
                    t.setTransactionType(rs.getString(2));
                    t.setAmount(rs.getDouble(3));
                    t.setTransactionDate(rs.getDate(4).toLocalDate());
                    t.setAccountName(rs.getString(5));
                    t.setCategoryName(rs.getString(6));
                    t.setDescription(rs.getString(7));
                    list.add(t);
                }
            }
        }
        return list;
    }

    /**
     * 添加新的交易记录
     */
    public boolean addTransaction(int userId, int accountId, int categoryId, String type,
                                  double amount, LocalDate date, String description) throws Exception {
        String sql = "INSERT INTO transactions(user_id, account_id, category_id, transaction_type, amount, transaction_date, description) " +
                     "VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, accountId);
            ps.setInt(3, categoryId);
            ps.setString(4, type);
            ps.setDouble(5, amount);
            ps.setDate(6, java.sql.Date.valueOf(date));
            ps.setString(7, description);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 更新交易记录
     */
    public boolean updateTransaction(Transaction t, int accountId, int categoryId) throws Exception {
        String sql = "UPDATE transactions SET account_id=?, category_id=?, transaction_type=?, amount=?, transaction_date=?, description=? " +
                     "WHERE transaction_id=?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, accountId);
            ps.setInt(2, categoryId);
            ps.setString(3, t.getTransactionType());
            ps.setDouble(4, t.getAmount());
            ps.setDate(5, java.sql.Date.valueOf(t.getTransactionDate()));
            ps.setString(6, t.getDescription());
            ps.setInt(7, t.getTransactionId());
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 删除交易记录
     */
    public boolean deleteTransaction(int transactionId) throws Exception {
        String sql = "DELETE FROM transactions WHERE transaction_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, transactionId);
            return ps.executeUpdate() > 0;
        }
    }
} 